﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using SmartMD.DataModel.Entity;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using SmartMD.DataModel;

namespace SmartMD.DataAccess.Oracle.DaoImpl
{
    public class ContainerModelDaoImpl : BaseGenericDaoImpl
    {
        public ObservableCollection<ContainerModelEntity> loadAllContainerModels()
        {
            /*ObservableCollection<ContainerModelEntity> containerModels = new ObservableCollection<ContainerModelEntity>();
            ContainerModelEntity item;
            StringBuilder sb = new StringBuilder();
            sb.Clear();
            sb.Append(" select *");
            sb.Append(" from DUO_CONTAINER_MODEL");

            DataTable dt = this.dbConn.Select(sb.ToString());
            if (dt != null)
            {
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    item = new ContainerModelEntity();
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        string clmName;
                        clmName = dt.Columns[i].ColumnName;
                        item.setValue(clmName, dt.Rows[j][clmName]);
                    }
                    containerModels.Add(item);
                }
                return containerModels;
            }
            else
                return null;          */
            ObservableCollection<ContainerModelEntity> containerModels = new ObservableCollection<ContainerModelEntity>();
            XMLHelper xmlOper = new XMLHelper();
            String conn_str = xmlOper.GetXmlNodeAttribute("SmartMD.Manager.vshost.exe.config", "configuration/appSettings/add[@key='DataAddress']", "value");
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection(conn_str);
            OleDbCommand cmd = new OleDbCommand("select *  from DUO_CONTAINER_MODEL", conn);
            da.SelectCommand = cmd;
            cmd.Connection.Open();
            da.Fill(ds);

            ContainerModelEntity item = null;
            //  byte[] img = new byte[0];
            for(int i=0; i<ds.Tables[0].Rows.Count; i++)
            {
                 DataRow dr;
                 item = new ContainerModelEntity();
                 dr = ds.Tables[0].Rows[i];              
                   
                   item.Id = (string)dr["ID"];
                   item.Name = (string)dr["NAME"];
                   item.ModelType = ContainerModelEntity.GetStyle((string)dr["MODELTYPE"]);
                   item.StyleFile = (byte[])dr["STYLEFILE"];
                  item.CreateDate = (DateTime)dr["CREATEDATE"];
                  containerModels.Add(item);
            
            }        
            conn.Close();
            return containerModels;
        }

        public bool SaveContainerModel(ContainerModelEntity containerModel)
        {           
               /* StringBuilder sb = new StringBuilder();

                sb.Clear();
                sb.Append(" insert into");
                sb.Append(" DUO_CONTAINER_MODEL (ID, NAME, STYLEFILE, CREATEDATE)");
                sb.Append(" values(");
                sb.Append(" '" + containerModel.Id + "',");
                sb.Append(" '" + containerModel.Name + "',");
                sb.Append(" '" + containerModel.StyleFile + "',");
                sb.Append("to_date ( '" + containerModel.CreateDate.ToString() + "' , 'YYYY-MM-DD HH24:MI:SS' )");
                sb.Append(")");
                bool isSaved = this.dbConn.Insert(sb.ToString());
 
                return isSaved; */
  
            //string conn_str = "Provider=OraOLEDB.Oracle.1;Password=agoodjob;Persist Security Info=True;User ID=ams;Data Source=ims;Extended Properties=";//Provider必須用OraOLEDB.Oracle.1
            XMLHelper xmlOper = new XMLHelper();
            String conn_str = xmlOper.GetXmlNodeAttribute("SmartMD.Manager.vshost.exe.config", "configuration/appSettings/add[@key='DataAddress']", "value");
            OleDbConnection cn = new OleDbConnection(conn_str);
            cn.Open();

            OleDbCommand cmd = new OleDbCommand("update DUO_CONTAINER_MODEL set STYLEFILE = ? where ID='" + containerModel.Id + "'", cn);
            cmd.Parameters.Add("STYLEFILE", OleDbType.Binary);
            cmd.Parameters["STYLEFILE"].Value = containerModel.StyleFile;
            try
            {
                int result = cmd.ExecuteNonQuery();
                if (result < 1)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("insert into DUO_CONTAINER_MODEL (ID, NAME, MODELTYPE, STYLEFILE, CREATEDATE)");
                    sb.Append("VALUES ("); 
                    sb.Append(" '" + containerModel.Id + "' ,");
                    sb.Append(" '" + containerModel.Name + "' ,");
                    sb.Append(" '" + containerModel.ModelType + "' ,");
                    sb.Append(" ? ,");
                    sb.Append("to_date ( '" + containerModel.CreateDate.ToString("yyyy-MM-dd HH:mm:ss") + "' , 'YYYY-MM-DD HH24:MI:SS' )");
                    sb.Append(" )");
                    OleDbCommand cmd1 = new OleDbCommand(sb.ToString(), cn);
                    cmd1.Parameters.Add("STYLEFILE", OleDbType.Binary);
                    cmd1.Parameters["STYLEFILE"].Value = containerModel.StyleFile;
                    result = cmd1.ExecuteNonQuery();
                }
                if (result >= 1)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return false;
        }

        public ContainerModelEntity GetObjectByName(string name)
        {
            XMLHelper xmlOper = new XMLHelper();
            String conn_str = xmlOper.GetXmlNodeAttribute("SmartMD.Manager.vshost.exe.config", "configuration/appSettings/add[@key='DataAddress']", "value");
            OleDbDataAdapter da = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            OleDbConnection conn = new OleDbConnection(conn_str);
            OleDbCommand cmd = new OleDbCommand("select *  from DUO_CONTAINER_MODEL WHERE NAME = '" + name + "'", conn);
            da.SelectCommand = cmd;
            cmd.Connection.Open();
            da.Fill(ds);

            ContainerModelEntity item = null;
            //  byte[] img = new byte[0];
            DataRow dr;
            dr = ds.Tables[0].Rows[0];

            if (!ds.Tables[0].Rows[0]["STYLEFILE"].ToString().Equals(""))
            {
                item = new ContainerModelEntity();         
                item.Id = (string)dr["ID"];
                item.Name = (string)dr["NAME"];
                item.ModelType = ContainerModelEntity.GetStyle((string)dr["MODELTYPE"]);
                item.StyleFile = (byte[])dr["STYLEFILE"];
                item.CreateDate = (DateTime)dr["CREATEDATE"];
            }
            conn.Close();
            return item;
        }

        public void DeleteObjectById(string id)
        {
            StringBuilder sb = new StringBuilder();
            sb.Clear();
            sb.Append(" delete from DUO_CONTAINER_MODEL");
            sb.Append(" where ID ='" + id + "'");

            this.dbConn.Delete(sb.ToString());
        }

       
    }
}
